package com.zmj.utils;

import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import com.alibaba.druid.pool.DruidDataSource;

public class DBUtils {
	private static DruidDataSource dataSource=new DruidDataSource();
	static String driver;
	static String url;
	static String username;
	static String password;
	static {
		try {
			Class.forName("com.mysql.jdbc.Driver");
			
//			InputStream in=DBUtils.class.getClassLoader().getResourceAsStream("jdbc.properties");
//			Properties pt=new Properties();
//			
//			pt.load(in);
//			
//			driver =pt.getProperty("driver");
//			url =pt.getProperty("url");
//			username =pt.getProperty("username");
//			password =pt.getProperty("password");
//			dataSource.setDriverClassName(driver);
//		    dataSource.setUrl(url);
//			dataSource.setUsername(username);
//		    dataSource.setPassword(password);
		}catch (Exception e){
			e.printStackTrace();
		}
			
	}
		
	public static Connection getConnection() {
		try {
			String url = "jdbc:mysql://127.0.0.1:3307/myshop?useUnicode=true&characterEncoding=utf-8&useSSL=true";
			return DriverManager.getConnection(url, "root", "zmj12345");
//			
//			Connection connection=dataSource.getConnection();
//			return connection;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
//private static DruidDataSource dataSource=new DruidDataSource();
//	
//	static {
//		dataSource.setDriverClassName("com.mysql.jdbc.Driver");
//		dataSource.setUrl("jdbc:mysql://127.0.0.1:3307/myshop?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone =Asia/Shanghai");
//		dataSource.setUsername("root");
//		dataSource.setPassword("zmj12345");
//	}
//	//获取连接
//	public static Connection getConnection() {
//		try {
//			Connection connection=dataSource.getConnection();
//			return connection;
//		} catch (SQLException e) {
//			// TODO Auto-generated catch block
//			e.printStackTrace();
//		}
//		return null;
//	}
	//关闭方法
	public static void release(ResultSet rs,Statement ps, Connection cn) {
		if(rs!=null) {
			try {
				rs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(ps!=null) {
			try {
				ps.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if(cn!=null) {
			try {
				cn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	//关闭方法的重载
	
	public static void release(Statement ps, Connection cn) {

		release(null, ps,cn);
	}
	
	
//	 DML语言的封装
	
	
	public static int executeDML(String sql,Object...params ) throws Exception{
		Connection cn=DBUtils.getConnection();
		PreparedStatement ps=cn.prepareStatement(sql);
		if (null!=params&&params.length>0) {
			for (int i = 0; i < params.length; i++) {
				ps.setObject(i + 1, params[i]);
			}
		}
		int num=ps.executeUpdate();
		
		return num;
		
	}
	
	
//	 查询单个元素的方法封装
	 
	
	public static <T> T executeDQLByOne(Class cls, String sql, Object... params) throws Exception{
		
		List<T> list = executeDQL(cls,sql,params);
		
		if(null != list && list.size() >0){
			return list.get(0);
		}
		
		return null;
		
	}
	
	//查询数量的方法封装
	public static int executeCount(String sql) throws Exception{
		Connection cn = DBUtils.getConnection();

		PreparedStatement ps = cn.prepareStatement(sql);
	
		ResultSet rs = ps.executeQuery();
		
		if(rs.next()){
			
			return rs.getInt(1);
			
		}
		return 0;
		
	}
	
	
//	查询多元素返回集合的封装
	
	public static <T> List<T> executeDQL(Class cls, String sql, Object... params) throws Exception {

		List<T> list = new ArrayList<T>();

		Connection cn = DBUtils.getConnection();

		PreparedStatement ps = cn.prepareStatement(sql);

		if (null != params && params.length > 0) {
			for (int i = 1; i <= params.length; i++) {
				ps.setObject(i, params[i - 1]);
			}
		}
		ResultSet rs = ps.executeQuery();
		ResultSetMetaData metaData = rs.getMetaData();
		int count = metaData.getColumnCount();
		
		T obj;
		
		while (rs.next()) { 
			obj = (T) cls.newInstance(); 

			for (int i = 1; i <= count; i++) {
				
				String columnName = metaData.getColumnLabel(i);
				
				Object columnValue = rs.getObject(columnName); 
				
				Field field = cls.getDeclaredField(columnName);

				if(columnValue != null){
					field.setAccessible(true);
					field.set(obj, columnValue);
				}
			}
			list.add(obj);
		}
		return list;
	}
	
}
